# 创建时间:2024/9/21 下午6:45
# 创建人:天霄
# 基于 Python 3.11
# ========================================
# 便捷方法
# ========================================
import sqlite3
import traceback
import datetime
from pathlib import Path


db_file = (Path.cwd() / 'battery.db')


def simplify_traceback(error: Exception):
    """简化错误堆栈信息，简化路径"""
    where = traceback.format_exc().replace(str(Path.cwd()), "")
    return f'{error.__class__.__name__}: {error}\nAt:\n{where}'


def init_db():
    """创建表结构"""
    tables = {
        # 电量变化记录
        'battery': '''
        CREATE TABLE IF NOT EXISTS battery (
            bid INTEGER PRIMARY KEY AUTOINCREMENT,
            health TEXT NOT NULL,
            percentage INTEGER NOT NULL,
            plugged TEXT NOT NULL,
            status TEXT NOT NULL,
            temperature REAL NOT NULL,
            `current` INTEGER NOT NULL,
            time_at TEXT NOT NULL
        )
        ''',
        # 充电/断电操作记录  is_charged=1：充电操作，is_charged=0：断电操作
        'charge': '''
        CREATE TABLE IF NOT EXISTS charge (
            cid INTEGER PRIMARY KEY AUTOINCREMENT,
            is_charged BOOLEAN NOT NULL,
            percentage INTEGER,
            `current` INTEGER,
            time_at TEXT NOT NULL
        )
        '''
    }
    with sqlite3.connect(db_file) as db:
        cursor = db.cursor()
        for table_name, query in tables.items():
            cursor.execute(f"SELECT name FROM sqlite_master WHERE type='table' AND name='{table_name}'")
            if cursor.fetchone() is None:
                cursor.execute(query)
        cursor.close()


def insert_battery_info(battery_info: dict):
    """记录一条电量记录

    battery_info格式：
            {
          "health": "GOOD",  // 表示电池的健康状况
          "percentage": 57,  // 表示电池的电量百分比，取值范围是0到100。
          "plugged": "UNPLUGGED",  // 表示设备是否连接了充电器，以及连接的充电器类型
          "status": "DISCHARGING",  // 表示电池的充电状态，取值有：CHARGING（正在充电）、DISCHARGING（正在放电）、NOT_CHARGING（未充电）、FULL（电池已充满）。
          "temperature": 14.100000381469727,  // 表示电池的温度，单位是摄氏度（°C）。
          "current": -72000  // 表示电池的电流，单位是微安（μA）。正值表示充电电流，负值表示放电电流。
        }
    """
    with sqlite3.connect(db_file) as db:
        cursor = db.cursor()
        sql = 'INSERT INTO battery (health, percentage, plugged, status, temperature, current, time_at) VALUES (?, ?, ?, ?, ?, ?, ?) '
        cursor.execute(sql,(battery_info['health'], battery_info['percentage'], battery_info['plugged'], battery_info['status'],
             battery_info['temperature'], battery_info['current'], datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')))
        db.commit()
        cursor.close()


def insert_charge_operation(is_charged: bool, percentage: int = None, current: int = None):
    """记录充电/断电操作"""
    with sqlite3.connect(db_file) as db:
        cursor = db.cursor()
        sql = 'INSERT INTO charge (is_charged, percentage, `current`, time_at) VALUES (?, ?, ?, ?) '
        cursor.execute(sql, (is_charged, percentage, current, datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')))
        db.commit()
        cursor.close()


def delete_old_records(keep_days: int):
    """删除数据库中保留时间之外的记录"""
    with sqlite3.connect(db_file) as db:
        cursor = db.cursor()
        now = datetime.datetime.now()
        limit_time = (now - datetime.timedelta(days=keep_days)).strftime('%Y-%m-%d %H:%M:%S')
        sql = f"DELETE FROM battery WHERE time_at < '{limit_time}' "
        cursor.execute(sql)
        db.commit()
        sql2 = f"DELETE FROM charge WHERE time_at < '{limit_time}' "
        cursor.execute(sql2)
        db.commit()
        cursor.close()
